﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Web;
using System.Text;
using System.Reflection;
using System.Linq;


namespace HDQApp.Common
{

    public abstract class DbHelperMsSQL
    {

        public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];


        #region 查询是否存在

        public static bool Exists(string strSql)
        {
            object obj = GetSingle<object>(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle<object>(strSql, 0, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion

        #region 单值、首行、DataTable、DataSet 查询

        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ") from " + TableName;
            object obj = GetSingle<object>(strsql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static T GetSingle<T>(string SQLString, int Times = 0)
        {
            return GetSingle<T>(SQLString, Times, null);
        }
        public static T GetSingle<T>(string SQLString, int Times, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms, Times);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return (T)Convert.ChangeType(obj, typeof(T));
                    }
                    catch (Exception ex)
                    {
                        connection.Close();

                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }

        public static DataRow QueryRow(string SQLString, int Times = 30)
        {
            return QueryRow(SQLString, Times, null);
        }
        public static DataRow QueryRow(string SQLString, int Times = 30, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                cmd.CommandTimeout = Times;
                if (cmdParms != null)
                {
                    for (int i = 0; i < cmdParms.Length; i++)
                    {
                        cmd.Parameters.Add(cmdParms[i]);
                    }
                }
                try
                {
                    SqlDataAdapter ada = new SqlDataAdapter(cmd);
                    ada.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                        return ds.Tables[0].Rows[0];
                    else
                        return null;
                }
                catch (Exception ex)
                {

                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        public static DataTable QueryTable(string SQLString, int Times = 30)
        {
            return QueryTable(SQLString, Times, null);
        }
        public static DataTable QueryTable(string SQLString, int Times = 30, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms, Times);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet("ds");
                    try
                    {
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        connection.Close();

                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return (ds == null || ds.Tables.Count <= 0) ? new DataTable("dt") : ds.Tables[0];
                }
            }
        }

        public static DataSet Query(string SQLString, int Times = 30)
        {
            return Query(SQLString, Times, null);
        }
        public static DataSet Query(string SQLString, int Times = 30, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms, Times);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        connection.Close();

                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return ds;
                }
            }
        }

        #endregion




        #region  执行SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }
        public static int ExecuteSqlByTimes(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();

                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch(Exception ex)
                {
                    tx.Rollback();
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlContent(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {

                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {

                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {

                throw e;
            }
        }

        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params IDataParameter[] cmdParms)
        {

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {

                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }

        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static bool ExecuteSqlTran(Hashtable SQLStringList,out string msg)
        {
            bool isOk = true;
            msg = string.Empty;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch(Exception ex)
                    {
                        trans.Rollback();
                        msg = ex.Message;
                        isOk = false;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return isOk;
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }


        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {

                throw e;
            }

        }




        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, IDataParameter[] cmdParms, int Times = 30)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (Times > 0)//（以秒为单位）。默认为 30 秒
            {
                cmd.CommandTimeout = Times;
            }
            if (cmdParms != null && cmdParms.Length > 0)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput
                        || parameter.Direction == ParameterDirection.Input)
                        && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion


        /// <summary>
        /// 将数据库中的值转换为对象
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static T ConvertToObjectFromDB<T>(T obj, DataRow row)
        {
            Type type = obj.GetType();
            System.Reflection.PropertyInfo[] propInfo = type.GetProperties();
            for (int i = 0; i < propInfo.Length; i++)
            {
                if (row.Table.Columns[propInfo[i].Name] != null && row[propInfo[i].Name] != System.DBNull.Value)
                {
                    try
                    {
                        propInfo[i].SetValue(obj, row[propInfo[i].Name], null);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            return obj;
        }
        /// <summary>
        /// 将datatable转换为List对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="type"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertToListObject<T>(DataTable dt)
        {

            List<T> objs = new List<T>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                T obj = (T)Activator.CreateInstance(typeof(T));
                obj = ConvertToObjectFromDB(obj, dt.Rows[i]);
                objs.Add(obj);
            }
            return objs;
        }




        #region 存储过程操作

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            return RunProcedure(storedProcName, parameters, null, out rowsAffected);
        }

        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, int? timeOut, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                if (timeOut.HasValue)
                {
                    command.CommandTimeout = timeOut.Value;
                }
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion



        #region 分页处理
        /// <summary>
        /// 通过 sp_GetListByPageAndFileds 存储过程执行
        /// </summary>
        /// <param name="pageSize">每页数据量</param>
        /// <param name="currentPage">当前页，自动默认为为1</param>
        /// <param name="fields">查询字段，可以用 * 表示所有</param>
        /// <param name="orderString">排序字段 + asc/desc</param>
        /// <param name="whereString">不包含‘where’的字符串</param>
        /// <param name="tablename">表名，或者为查询得出的 子表 ，子表查询sql需要括号括起来，并指定新表名(暂不支持含有单引号的sql查询表)</param>
        /// <param name="count">返回总条数</param>
        /// <returns></returns>
        public static DataTable GetDataTableByPager(int pageSize, int currentPage, string fields, string orderString, string whereString, string tablename, out int count)
        {
            if (string.IsNullOrWhiteSpace(fields))
            {
                fields = "*";
            }
            count = 0;
            DataTable table = new DataTable("dt");
            SqlParameter[] param ={ new SqlParameter("@pageSize",pageSize) ,
                new SqlParameter("@currentPage",currentPage) ,
                new SqlParameter("@fields",fields) ,
                new SqlParameter("@orderString",orderString) ,
                new SqlParameter("@tablename",tablename) ,
                new SqlParameter("@whereString",whereString) ,
            };
            DataSet ds = new DataSet();
            ds = ExecProcedure("sp_GetListByPageAndFileds", param);
            table = ds.Tables[0];
            count = Convert.ToInt32(ds.Tables[1].Rows[0]["cnt"].ToString());
            return table;
        }

        #region 分页存储过程 脚本
        //CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds]
        // ( 
        //     @pageSize  int,				/**  每页数据量        **/   
        //     @currentPage int = 1,		/**  当前页，默认为为1 **/       
        //     @fields   varchar(2000),	/**  查询字段，可以用 * 表示所有 **/     
        //     @tablename  varchar(max),   /**  表名，或者为查询得出的 子表 ，子表查询sql需要括号括起来，并指定新表名  **/     
        //     @orderString varchar(1000), /**  排序字段 + asc/desc   **/       
        //     @whereString varchar(1000)  /**  不包含‘where’的字符串 **/      
        // )
        //     AS
        //     BEGIN
        //      DECLARE @sql varchar(2000)
        //      DECLARE @strOrder varchar(2000)
        //      DECLARE @strWhere varchar(2000)
        //      declare @recordcount int 
        //      declare @convertorderstr varchar(2000)
        //     declare @countsql nvarchar(4000)
        //     declare @totalpage int
        //      set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','')
        //      if @strOrder != ''
        //       set @strOrder = ' order by ' + @strOrder
        //      else
        //       set @strOrder = ' order by ID DESC'
        //     set @strOrder=lower(@strOrder)
        //     set @convertorderstr=replace(@strOrder,'desc','d_e_s_c')
        //     set @convertorderstr=replace(@convertorderstr,'asc','desc')
        //     set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc')
        //      set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','')
        //      if @strWhere != ''
        //       set @strWhere = ' where ' + @strWhere

        //     set @countsql='select @a=count(*) from ' + @tablename + @strWhere
        //     exec  sp_executesql @countsql,N'@a int output',@recordcount output 


        //      if @pageSize = 0
        //       set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder
        //      else
        //      begin
        //             if @recordcount%@pageSize=0
        //                 set @totalpage=@recordcount/@pageSize
        //             else
        //                 set @totalpage=@recordcount/@pageSize+1
        //         if @totalpage <=1
        //          set @currentPage=1 
        //          if @totalpage <@currentPage 
        //           set @currentPage=@totalpage
        //       if @currentPage = 1
        //        set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder
        //       else
        //         if (@currentPage - 1) * @pageSize > @recordcount / 2
        //             set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1  ' + @strOrder
        //         else
        //             set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1  ' + @convertorderstr + ') as t2  ' + @strOrder
        //         end
        //         set @sql = @sql + '; select '+str(@recordcount)+' as cnt'
        //      exec(@sql)
        //     END
        #endregion
        #endregion


        public static string GetInsertSql<T>(object model, string[] excludeFields, out List<SqlParameter> lstParam, params Attribute[] excludeAttr)
        {
            Type tyTable = typeof(T);
            Hashtable ht = ConvertHashTable<T>(model, excludeFields, excludeAttr);
            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("insert into " + tyTable.Name + " (");
            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(") values (");

            string insert_sql = string.Empty;
            DbParameter[] paras = null;
            lstParam = new List<SqlParameter>();
            foreach (DictionaryEntry entry in ht)
            {
                string name = entry.Key.ToString();
                string parameterName = "@" + name;
                object objvalue = entry.Value;

                sb_beforeSQL.Append(name + ",");
                sb_afterSQl.Append(parameterName + ",");

                SqlParameter para = new SqlParameter();
                para.ParameterName = parameterName;
                para.Value = (objvalue ?? DBNull.Value);

                if (objvalue != null)
                {
                    para.DbType = GetValueDbType(objvalue, para);
                }
                lstParam.Add(para);
            }
            insert_sql = sb_beforeSQL.ToString().TrimEnd(',');
            insert_sql += sb_afterSQl.ToString().TrimEnd(',') + ")";
            paras = lstParam.ToArray();
            return insert_sql;
        }
        public static bool Insertmodel<T>(object model, string[] excludeFields, params Attribute[] excludeAttr)
        {
            Type tyTable = typeof(T);
            Hashtable ht = ConvertHashTable<T>(model, excludeFields, excludeAttr);
            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("insert into " + tyTable.Name + " (");
            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(") values (");

            string insert_sql = string.Empty;
            DbParameter[] paras = null;
            List<DbParameter> lstParam = new List<DbParameter>();
            foreach (DictionaryEntry entry in ht)
            {
                string name = entry.Key.ToString();
                string parameterName = "@" + name;
                object objvalue = entry.Value;

                sb_beforeSQL.Append(name + ",");
                sb_afterSQl.Append(parameterName + ",");

                SqlParameter para = new SqlParameter();
                para.ParameterName = parameterName;
                para.Value = (objvalue ?? DBNull.Value);

                if (objvalue != null)
                {
                    para.DbType = GetValueDbType(objvalue, para);
                }
                lstParam.Add(para);
            }
            insert_sql = sb_beforeSQL.ToString().TrimEnd(',');
            insert_sql += sb_afterSQl.ToString().TrimEnd(',') + ")";
            paras = lstParam.ToArray();
            int res = ExecuteSql(insert_sql, paras);
            return res > 0 ? true : false;
        }

        public static string GetUpdateSql<T>(object model, string[] excludeFields, out List<SqlParameter> lstParam, Attribute[] excludeAttr, string uniqueName = "Id")
        {
            Type tyTable = typeof(T);
            Hashtable ht = ConvertHashTable<T>(model, excludeFields, excludeAttr);

            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("update " + tyTable.Name + " set ");

            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(" where " + uniqueName.ToLower() + "=" + ("@" + uniqueName.ToLower()) + "");

            string update_sql = string.Empty;
            DbParameter[] paras = null;
            lstParam = new List<SqlParameter>();
            foreach (DictionaryEntry kv in ht)
            {
                string name = kv.Key.ToString().ToLower();
                string parameterName = "@" + name;

                if (!name.Equals(uniqueName, StringComparison.OrdinalIgnoreCase))
                {
                    sb_beforeSQL.Append(kv.Key + "=" + parameterName + ",");
                }

                object objvalue = kv.Value;
                SqlParameter para = new SqlParameter();
                para.ParameterName = parameterName;
                para.Value = (objvalue ?? DBNull.Value);
                if (objvalue != null)
                {
                    para.DbType = GetValueDbType(objvalue, para);
                }
                lstParam.Add(para);
            }
            update_sql = sb_beforeSQL.ToString().TrimEnd(',');
            update_sql += sb_afterSQl.ToString();
            return update_sql;
        }

        public static int UpdateModel<T>(object model, string[] excludeFields, string uniqueName = "Id", params Attribute[] excludeAttr)
        {
            Type tyTable = typeof(T);
            Hashtable ht = ConvertHashTable<T>(model, excludeFields, excludeAttr);

            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("update " + tyTable.Name + " set ");

            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(" where " + uniqueName.ToLower() + "=" + ("@" + uniqueName.ToLower()) + "");

            string update_sql = string.Empty;
            DbParameter[] paras = null;
            List<DbParameter> lstParam = new List<DbParameter>();
            foreach (DictionaryEntry kv in ht)
            {
                string name = kv.Key.ToString().ToLower();
                string parameterName = "@" + name;

                if (!name.Equals(uniqueName, StringComparison.OrdinalIgnoreCase))
                {
                    sb_beforeSQL.Append(kv.Key + "=" + parameterName + ",");
                }

                object objvalue = kv.Value;
                SqlParameter para = new SqlParameter();
                para.ParameterName = parameterName;
                para.Value = (objvalue ?? DBNull.Value);
                if (objvalue != null)
                {
                    para.DbType = GetValueDbType(objvalue, para);
                }
                lstParam.Add(para);
            }
            update_sql = sb_beforeSQL.ToString().TrimEnd(',');
            update_sql += sb_afterSQl.ToString();
            paras = lstParam.ToArray();
            int res = ExecuteSql(update_sql, paras);
            return res;
        }

        private static Hashtable ConvertHashTable<T>(object KV, string[] excludeFields, params Attribute[] excludeAttr)
        {
            Hashtable ht = new Hashtable();

            List<string> lstExcludeField = new List<string>();
            if (excludeFields != null && excludeFields.Length > 0)
            {
                lstExcludeField = excludeFields.Select(t => t.ToLower()).ToList();
            }
            List<string> lstAllField = new List<string>();
            Type tyTable = typeof(T);
            PropertyInfo[] pyInfos = tyTable.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.DeclaredOnly);
            foreach (PropertyInfo pInfo in pyInfos)
            {
                if (excludeAttr != null && excludeAttr.Length > 0)
                {
                    bool isBK = false;
                    Attribute[] attrs = pInfo.GetCustomAttributes(true) as Attribute[];
                    if (attrs != null && attrs.Length > 0)
                    {
                        foreach (var attr in attrs)
                        {
                            foreach (var item in excludeAttr)
                            {
                                if (attr.GetType() == item.GetType())
                                {
                                    isBK = true;
                                    break;
                                }
                            }
                            if (isBK)
                            {
                                break;
                            }
                        }
                        if (isBK)
                        {
                            continue;
                        }
                    }
                }
                string name = pInfo.Name.ToString().ToLower();
                object objvalue = null;

                if (KV is T && !lstExcludeField.Contains(name))
                {
                    objvalue = pInfo.GetValue((T)KV, null);
                    ht.Add(name, objvalue);
                }

                //存储对象 所有小写形式的属性
                lstAllField.Add(name);
            }

            if (KV is IDictionary)
            {
                IDictionary dict = ((IDictionary)KV);
                foreach (DictionaryEntry entry in dict)
                {
                    string name = entry.Key.ToString().ToLower();
                    if (lstAllField.Contains(name) && !lstExcludeField.Contains(name))
                    {
                        ht.Add(name, entry.Value);
                    }
                }
            }
            else if (KV is NameValueCollection)
            {
                NameValueCollection nvc = KV as NameValueCollection;
                foreach (string key in nvc.AllKeys)
                {
                    string name = key.ToLower();
                    if (lstAllField.Contains(name) && !lstExcludeField.Contains(name))
                    {
                        ht.Add(name, nvc[key]);
                    }
                }
            }

            return ht;
        }

        private static DbType GetValueDbType(object objvalue, DbParameter para)
        {
            Type tyval = objvalue.GetType();
            Type typeVal = Nullable.GetUnderlyingType(tyval) ?? tyval;

            DbType dbType = DbType.AnsiString;
            DateTime dtime;
            Guid gid;
            if (typeVal.IsEnum && Enum.GetUnderlyingType(typeVal) == typeof(byte))
            {
                dbType = DbType.Int32;
            }
            else if (typeVal == typeof(byte[]))
            {
                dbType = DbType.Binary;
            }
            else if (DateTime.TryParse(objvalue.ToString(), out dtime))
            {
                dbType = DbType.DateTime;
            }
            else if (Guid.TryParse(objvalue.ToString(), out gid))
            {
                dbType = DbType.String;
                para.Value = objvalue.ToString();
            }
            else
            {

            }
            return dbType;
        }
    }


}
